Transactional database systems are commonly implemented in a client-server environment in which applications access a database server to obtain data in response to requests submitted to the database server. For example, a relational database management system may be implemented on a server and applications will access the server by issuing relational database statements to the server. The database server will return results and/or error codes to the applications. In a transactional database system, records may be altered by an application, but until the application commits the changes, the altered database data will generally not be accessed by other applications. Database resources may be used by an application in processing a transaction and during the transaction such resources will be therefore unavailable to other applications. When the application ends the transaction by a commit or a rollback the database system resources will be released for potential use by all applications.
An example of database system resources that are made unavailable during a transaction are locks. In many cases, database management systems are designed to permit multiple applications to concurrently access data. To allow such concurrent access, database management systems typically include locks that are available for acquisition by applications to ensure the correct serialization of access to data. Database applications protect changed (inserted/deleted/updated) but uncommitted records by placing a lock on such changed records (in relational systems such a lock is a row lock). Such a lock prevents concurrent applications from accessing uncommitted records. An application holding one or more locks will release all the locks that the application holds when the current transaction ends with either a commit or a rollback.
According to certain database designs, each application is provided with a user configurable upper limit of on the number of locks that may be held at any one time. In addition, a system will have an implicit or explicit limit on the total number of locks held by all applications. Once this limit is reached, any attempt to lock an additional record may result in lock escalation. In lock escalation the application locks the target table (or other database structure) to protect all records and to allow the release of the previously held row locks. As a result, locks are made available in the system (typically, all locks require the same amount of system resources, so replacing multiple row locks with a single table lock frees system resources).
An online application is one that does not limit other concurrent applications from accessing committed (although not uncommitted) data in both read and write mode. An application holding a table lock is by definition offline, because concurrent applications are prevented from accessing the table. Therefore, for an application to stay online the application must be able to avoid lock escalation. For this reason, it is desirable to ensure that applications do not reach their defined limit of held locks.
Another system resource that can become a limiting factor in a database system that supports multiple concurrent online applications is the active log space. A characteristic of certain database management systems is that certain changes to data (for example, a change made in a relational database using SQL statements, as opposed to some data movement utilities) will result in log records being written prior to updating the database. Such logs are used in the process of database recovery in which the database is returned to a consistent state after a failure (crash recovery, or a roll-forward recovery following a database restore from a backup). If the space needed to log the change is not available in the database logs, the database language statement will fail and in most cases the current transaction will be rolled back. Active log space consumed by an application cannot be freed until the active transaction ends (commit or rollback). Since log space is shared between all applications using the same database, this resource may become scarce when multiple applications are running concurrently.
To increase the availability of resources such as record locks and log space to applications in a database environment, an autocommit protocol may be adopted. In such an approach, where possible, an application will commit the current transaction after every database system statement is issued (for example after the each SQL statement is issued in a relational database system). This provides for the release to other applications of system resources that are held by the application until the end of a transaction. After each commit, the resources, such as locks and the active log space, are made available in the database system. However, there is a cost incurred resulting from performing commits based only on the issuance of database system statements and not based on the actual or predicted demand for system resources. This may be particularly inefficient in distributed environments (multiple physical machines running the same instance of a database manager) when multiple requests might have to be executed on different database nodes.
It is therefore desirable to provide a method and system for applications to potentially release system resources when a server determines that system resources have become scarce.